﻿using System;
using System.Drawing;
using System.Collections;
// using System.Collections.Generic;

using System.Reflection;
using System.Runtime.CompilerServices;
using System.ComponentModel;

using Microsoft.VisualBasic;
using Microsoft.VisualBasic.CompilerServices;
using Excel = Microsoft.Office.Interop.Excel;

using MT.eVotePRS.Model;
using System.Data;

namespace MT.eVotePRS.Presentation.Util
{
    public class ExcelAPIHelper
    {
        Object missingValue = Missing.Value;

        Excel.Range range;
        Excel.Application objExcel = null;
        Excel.Workbook objWorkbook = null;
        Excel.Worksheet objsheet = null;

        public ExcelAPIHelper()
        {
            objExcel = new Excel.Application();
            objWorkbook = objExcel.Workbooks.Add(Missing.Value);
            
            while (objWorkbook.Sheets.Count > 1)
            {
                objsheet = objWorkbook.Sheets.get_Item(2) as Excel.Worksheet;
                objsheet.Delete();
            }
            
            objsheet = objWorkbook.ActiveSheet as Excel.Worksheet;
        }

        void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch
            {
                obj = null;
            }
            finally
            {
                GC.Collect();
            }
        }

        void writtenHeader(string subTitle, int totalRec, int columns, DateTime dtFrom, DateTime? dtTo)
        {
            objsheet.Cells[1, 2] = "HKUST";
            objsheet.Cells[2, 2] = subTitle;
            objsheet.Cells[3, 2] = "";
            if (dtFrom != null && dtTo != null)
            {
                objsheet.Cells[4, 2] = "Period  :";
                objsheet.Cells[4, 3] = dtFrom.ToString("yyyy/MM/dd") + " ~ " + ((DateTime)dtTo).ToString("yyyy/MM/dd");
            }
            else
            {
                objsheet.Cells[4, 2] = "Date/Time:";
                objsheet.Cells[4, 3] = dtFrom.ToString("yyyy/MM/dd HH:mm:ss");
            }
            objsheet.Cells[5, 2] = "Quantity :";
            objsheet.Cells[5, 3] = totalRec;

            Char chrCol = (Char)(65 + columns);

            range = objsheet.Range[string.Format("A1:{0}1", chrCol), RuntimeHelpers.GetObjectValue(missingValue)];
            range.Merge(true);
            range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
            range.Font.Bold = true;
            range.Font.Size = 15;

            range = objsheet.Range["A1:B6", RuntimeHelpers.GetObjectValue(missingValue)];
            range.Font.Bold = true;
            range = objsheet.Range["B2:C2", RuntimeHelpers.GetObjectValue(missingValue)];
            range.Merge(true);
            range.HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
        }

        public void RptInvHandsetEvents(Status status, IList lstRecords, DateTime dtFrom, DateTime dtTo)
        {
            try
            {
                string subTitle = string.Format("List of Handsets with {0} Events", status.StatusDesc);

                int cols = 0;
                int rowNum = 7;

                if (status.Id == Constants.Status_CheckOut.Id || status.Id == Constants.Status_CheckIn.Id)
                {
                    cols = 4;
                    objsheet.Cells[rowNum, 1] = "Handset ID";
                    objsheet.Cells[rowNum, 2] = "Student ID";
                    objsheet.Cells[rowNum, 3] = "Checked-out Date/Time";
                    objsheet.Cells[rowNum, 4] = "Remark";
                }
                else if (status.Id == Constants.Status_Lost.Id)
                {
                    cols = 5;
                    objsheet.Cells[rowNum, 1] = "Handset ID";
                    objsheet.Cells[rowNum, 2] = "Student ID";
                    objsheet.Cells[rowNum, 3] = string.Format("{0} Date/Time", status.StatusDesc);
                    objsheet.Cells[rowNum, 4] = "Payment Note No.";
                    objsheet.Cells[rowNum, 5] = "Remark";
                }
                else if (status.Id == Constants.Status_Fault.Id)
                {
                    cols = 6;
                    objsheet.Cells[rowNum, 1] = "Handset ID";
                    objsheet.Cells[rowNum, 2] = "Student ID";
                    objsheet.Cells[rowNum, 3] = string.Format("{0} Date/Time", status.StatusDesc);
                    objsheet.Cells[rowNum, 4] = "Misused?";
                    objsheet.Cells[rowNum, 5] = "Payment Note No.";
                    objsheet.Cells[rowNum, 6] = "Remark";
                }
                else if (status.Id == Constants.Status_Found.Id || status.Id == Constants.Status_FoundnFault.Id || status.Id == Constants.Status_Repaired.Id)
                {
                    cols = 3;
                    objsheet.Cells[rowNum, 1] = "Handset ID";                    
                    objsheet.Cells[rowNum, 2] = string.Format("{0} Date/Time", status.StatusDesc);
                    objsheet.Cells[rowNum, 3] = "Remark";
                }

                writtenHeader(subTitle, lstRecords.Count, cols, dtFrom, dtTo);

                int count = lstRecords.Count;

                range = objsheet.Range[("A" + rowNum.ToString() + ":D" + Convert.ToString(rowNum)), RuntimeHelpers.GetObjectValue(missingValue)];
                range.Font.Color = Color.Black.ToArgb();
                range.Font.Bold = true;
                range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                range.Cells.Borders.LineStyle = Excel.XlBordersIndex.xlDiagonalDown;
                range.Borders.Color = ColorTranslator.ToOle(Color.Black);
                range = objsheet.Range[("A" + (rowNum+1).ToString() + ":A" + Convert.ToString(rowNum+1+count)), RuntimeHelpers.GetObjectValue(missingValue)];
                range.NumberFormatLocal = "@";
                range.ColumnWidth = 12;
                range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                range = objsheet.Range[("B" + (rowNum + 1).ToString() + ":B" + Convert.ToString(rowNum + 1 + count)), RuntimeHelpers.GetObjectValue(missingValue)];
                range.NumberFormatLocal = "@";
                range.ColumnWidth = 16;
                range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                range = objsheet.Range[("C" + (rowNum + 1).ToString() + ":C" + Convert.ToString(rowNum + 1 + count)), RuntimeHelpers.GetObjectValue(missingValue)];
                range.NumberFormatLocal = "@";
                range.ColumnWidth = 30;
                range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                range = objsheet.Range[("D" + (rowNum + 1).ToString() + ":D" + Convert.ToString(rowNum + 1 + count)), RuntimeHelpers.GetObjectValue(missingValue)];
                range.NumberFormatLocal = "@";
                range.ColumnWidth = 30;                
                range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;

                // list out the records.
                foreach (InventoryTxn txn in lstRecords)
                {
                    rowNum++;

                    if (status.Id == Constants.Status_CheckOut.Id || status.Id == Constants.Status_CheckIn.Id)
                    {
                        objsheet.Cells[rowNum, 1] = txn.HandsetID;
                        objsheet.Cells[rowNum, 2] = txn.StudentID;
                        objsheet.Cells[rowNum, 3] = txn.TxnTime;
                        objsheet.Cells[rowNum, 4] = txn.Remark;
                    }
                    else if (status.Id == Constants.Status_Lost.Id)
                    {
                        objsheet.Cells[rowNum, 1] = txn.HandsetID;
                        objsheet.Cells[rowNum, 2] = txn.StudentID;
                        objsheet.Cells[rowNum, 3] = txn.TxnTime;
                        objsheet.Cells[rowNum, 4] = txn.PaymentNo;
                        objsheet.Cells[rowNum, 5] = txn.Remark;
                    }
                    else if (status.Id == Constants.Status_Fault.Id)
                    {
                        objsheet.Cells[rowNum, 1] = txn.HandsetID;
                        objsheet.Cells[rowNum, 2] = txn.StudentID;
                        objsheet.Cells[rowNum, 3] = txn.TxnTime;
                        objsheet.Cells[rowNum, 4] = txn.NeedPay ? "Yes" : "";
                        objsheet.Cells[rowNum, 5] = txn.PaymentNo;
                        objsheet.Cells[rowNum, 6] = txn.Remark;
                    }
                    else if (status.Id == Constants.Status_Found.Id || status.Id == Constants.Status_FoundnFault.Id || status.Id == Constants.Status_Repaired.Id)
                    {
                        objsheet.Cells[rowNum, 1] = txn.HandsetID;
                        objsheet.Cells[rowNum, 2] = txn.TxnTime;
                        objsheet.Cells[rowNum, 3] = txn.Remark;
                    }
                }

                objsheet.Range["A1:A1", RuntimeHelpers.GetObjectValue(missingValue)].Select();
            }
            catch
            {

            }
            finally
            {
                releaseObject(objsheet);
                releaseObject(objWorkbook);
                releaseObject(objExcel);
            }
        }

        public void RptInvStudentEvents(Status status, IList lstRecords, DateTime dtFrom, DateTime dtTo)
        {
            try
            {
                string subTitle = string.Format("List of Handsets with {0} Events", status.StatusDesc);

                int cols = 4;
                int rowNum = 7;

                if (status.Id == Constants.Status_CheckOut.Id || status.Id == Constants.Status_CheckIn.Id)
                {
                    cols = 4;
                    objsheet.Cells[rowNum, 1] = "Student ID";
                    objsheet.Cells[rowNum, 2] = "Handset ID";
                    objsheet.Cells[rowNum, 3] = string.Format("{0} Date/Time", status.StatusDesc);
                    objsheet.Cells[rowNum, 4] = "Remark";
                }
                else if (status.Id == Constants.Status_Fault.Id)
                {
                    cols = 6;
                    objsheet.Cells[rowNum, 1] = "Student ID";
                    objsheet.Cells[rowNum, 2] = "Handset ID";
                    objsheet.Cells[rowNum, 3] = string.Format("{0} Date/Time", status.StatusDesc);
                    objsheet.Cells[rowNum, 4] = "Misused?";
                    objsheet.Cells[rowNum, 5] = "Payment Note No.";
                    objsheet.Cells[rowNum, 6] = "Remark";
                }
                else if (status.Id == Constants.Status_Lost.Id)
                {
                    cols = 5;
                    objsheet.Cells[rowNum, 1] = "Student ID";
                    objsheet.Cells[rowNum, 2] = "Handset ID";
                    objsheet.Cells[rowNum, 3] = string.Format("{0} Date/Time", status.StatusDesc);
                    objsheet.Cells[rowNum, 4] = "Payment Note No.";
                    objsheet.Cells[rowNum, 5] = "Remark";
                }

                writtenHeader(subTitle, lstRecords.Count, cols, dtFrom, dtTo);

                int count = lstRecords.Count;
                range = objsheet.Range[("A" + rowNum.ToString() + ":D" + Convert.ToString(rowNum)), RuntimeHelpers.GetObjectValue(missingValue)];
                range.Font.Color = Color.Black.ToArgb();
                range.Font.Bold = true;
                range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                range.Cells.Borders.LineStyle = Excel.XlBordersIndex.xlDiagonalDown;
                range.Borders.Color = ColorTranslator.ToOle(Color.Black);
                range = objsheet.Range[("A" + (rowNum + 1).ToString() + ":A" + Convert.ToString(rowNum + 1 + count)), RuntimeHelpers.GetObjectValue(missingValue)];
                range.NumberFormatLocal = "@";
                range.ColumnWidth = 12;
                range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                range = objsheet.Range[("B" + (rowNum + 1).ToString() + ":B" + Convert.ToString(rowNum + 1 + count)), RuntimeHelpers.GetObjectValue(missingValue)];
                range.NumberFormatLocal = "@";
                range.ColumnWidth = 16;
                range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                range = objsheet.Range[("C" + (rowNum + 1).ToString() + ":C" + Convert.ToString(rowNum + 1 + count)), RuntimeHelpers.GetObjectValue(missingValue)];
                range.NumberFormatLocal = "@";
                range.ColumnWidth = 30;
                range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                range = objsheet.Range[("D" + (rowNum + 1).ToString() + ":D" + Convert.ToString(rowNum + 1 + count)), RuntimeHelpers.GetObjectValue(missingValue)];
                range.NumberFormatLocal = "@";
                range.ColumnWidth = 30;
                range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;

                // list out the records.

                foreach (InventoryTxn txn in lstRecords)
                {
                    rowNum++;

                    if (status.Id == Constants.Status_CheckOut.Id || status.Id == Constants.Status_CheckIn.Id)
                    {
                        objsheet.Cells[rowNum, 1] = txn.StudentID;
                        objsheet.Cells[rowNum, 2] = txn.HandsetID;
                        objsheet.Cells[rowNum, 3] = txn.TxnTime;
                        objsheet.Cells[rowNum, 4] = txn.Remark;
                    }
                    else if (status.Id == Constants.Status_Fault.Id)
                    {
                        objsheet.Cells[rowNum, 1] = txn.StudentID;
                        objsheet.Cells[rowNum, 2] = txn.HandsetID;
                        objsheet.Cells[rowNum, 3] = txn.TxnTime;
                        objsheet.Cells[rowNum, 4] = txn.NeedPay ? "Yes" : "";
                        objsheet.Cells[rowNum, 5] = txn.PaymentNo;
                        objsheet.Cells[rowNum, 6] = txn.Remark;
                    }
                    else if (status.Id == Constants.Status_Lost.Id)
                    {
                        objsheet.Cells[rowNum, 1] = txn.StudentID;
                        objsheet.Cells[rowNum, 2] = txn.HandsetID;
                        objsheet.Cells[rowNum, 3] = txn.TxnTime;
                        objsheet.Cells[rowNum, 4] = txn.PaymentNo;
                        objsheet.Cells[rowNum, 5] = txn.Remark;
                    }

                }

                objsheet.Range["A1:A1", RuntimeHelpers.GetObjectValue(missingValue)].Select();
            }
            catch
            {

            }
        }

        public void RptInvHandsetStatus(Status status, IList lstRecords, DateTime dtTo)
        {
            try
            {
                string subTitle = string.Format("List of Handsets with {0} Status", status.StatusDesc);
                
                int cols = 0;
                int rowNum = 7;
                if (status.Id == Constants.Status_CheckOut.Id || status.Id == Constants.Status_CheckIn.Id)
                {
                    cols = 4;
                    objsheet.Cells[rowNum, 1] = "Handset ID";
                    objsheet.Cells[rowNum, 2] = "Student ID";
                    objsheet.Cells[rowNum, 3] = "Checked-out Date/Time";
                    objsheet.Cells[rowNum, 4] = "Remark";
                }
                else if (status.Id == Constants.Status_Lost.Id)
                {
                    cols = 5;
                    objsheet.Cells[rowNum, 1] = "Handset ID";
                    objsheet.Cells[rowNum, 2] = "Student ID";
                    objsheet.Cells[rowNum, 3] = string.Format("{0} Date/Time", status.StatusDesc);
                    objsheet.Cells[rowNum, 4] = "Payment Note No.";
                    objsheet.Cells[rowNum, 5] = "Remark";
                }
                else if (status.Id == Constants.Status_Fault.Id)
                {
                    cols = 6;
                    objsheet.Cells[rowNum, 1] = "Handset ID";
                    objsheet.Cells[rowNum, 2] = "Student ID";
                    objsheet.Cells[rowNum, 3] = string.Format("{0} Date/Time", status.StatusDesc);
                    objsheet.Cells[rowNum, 4] = "Misused?";
                    objsheet.Cells[rowNum, 5] = "Payment Note No.";
                    objsheet.Cells[rowNum, 6] = "Remark";
                }
                else if (status.Id == Constants.Status_Found.Id || status.Id == Constants.Status_FoundnFault.Id || status.Id == Constants.Status_Repaired.Id)
                {
                    cols = 3;
                    objsheet.Cells[rowNum, 1] = "Handset ID";
                    objsheet.Cells[rowNum, 2] = string.Format("{0} Date/Time", status.StatusDesc);
                    objsheet.Cells[rowNum, 3] = "Remark";
                }

                writtenHeader(subTitle, lstRecords.Count, cols, dtTo, null);

                int count = lstRecords.Count;
                range = objsheet.Range[("A" + rowNum.ToString() + ":D" + Convert.ToString(rowNum)), RuntimeHelpers.GetObjectValue(missingValue)];
                range.Font.Color = Color.Black.ToArgb();
                range.Font.Bold = true;
                range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                range.Cells.Borders.LineStyle = Excel.XlBordersIndex.xlDiagonalDown;
                range.Borders.Color = ColorTranslator.ToOle(Color.Black);
                range = objsheet.Range[("A" + (rowNum + 1).ToString() + ":A" + Convert.ToString(rowNum + 1 + count)), RuntimeHelpers.GetObjectValue(missingValue)];
                range.NumberFormatLocal = "@";
                range.ColumnWidth = 12;
                range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                range = objsheet.Range[("B" + (rowNum + 1).ToString() + ":B" + Convert.ToString(rowNum + 1 + count)), RuntimeHelpers.GetObjectValue(missingValue)];
                range.NumberFormatLocal = "@";
                range.ColumnWidth = 16;
                range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                range = objsheet.Range[("C" + (rowNum + 1).ToString() + ":C" + Convert.ToString(rowNum + 1 + count)), RuntimeHelpers.GetObjectValue(missingValue)];
                range.NumberFormatLocal = "@";
                range.ColumnWidth = 30;
                range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                range = objsheet.Range[("D" + (rowNum + 1).ToString() + ":D" + Convert.ToString(rowNum + 1 + count)), RuntimeHelpers.GetObjectValue(missingValue)];
                range.NumberFormatLocal = "@";
                range.ColumnWidth = 30;
                range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                
                // list out the records.

                foreach (InventoryTxn txn in lstRecords)
                {
                    rowNum++;

                    if (status.Id == Constants.Status_CheckOut.Id || status.Id == Constants.Status_CheckIn.Id)
                    {
                        objsheet.Cells[rowNum, 1] = txn.HandsetID;
                        objsheet.Cells[rowNum, 2] = txn.StudentID;
                        objsheet.Cells[rowNum, 3] = txn.TxnTime;
                        objsheet.Cells[rowNum, 4] = txn.Remark;
                    }
                    else if (status.Id == Constants.Status_Lost.Id)
                    {
                        objsheet.Cells[rowNum, 1] = txn.HandsetID;
                        objsheet.Cells[rowNum, 2] = txn.StudentID;
                        objsheet.Cells[rowNum, 3] = txn.TxnTime;
                        objsheet.Cells[rowNum, 4] = txn.PaymentNo;
                        objsheet.Cells[rowNum, 5] = txn.Remark;
                    }
                    else if (status.Id == Constants.Status_Fault.Id)
                    {
                        objsheet.Cells[rowNum, 1] = txn.HandsetID;
                        objsheet.Cells[rowNum, 2] = txn.StudentID;
                        objsheet.Cells[rowNum, 3] = txn.TxnTime;
                        objsheet.Cells[rowNum, 4] = txn.NeedPay ? "Yes" : "";
                        objsheet.Cells[rowNum, 5] = txn.PaymentNo;
                        objsheet.Cells[rowNum, 6] = txn.Remark;
                    }
                    else if (status.Id == Constants.Status_Found.Id || status.Id == Constants.Status_FoundnFault.Id || status.Id == Constants.Status_Repaired.Id)
                    {
                        objsheet.Cells[rowNum, 1] = txn.HandsetID;
                        objsheet.Cells[rowNum, 2] = txn.TxnTime;
                        objsheet.Cells[rowNum, 3] = txn.Remark;
                    }
                }

                objsheet.Range["A1:A1", RuntimeHelpers.GetObjectValue(missingValue)].Select();
            }
            catch
            {

            }
        }

        public void RptInvStudentStatus(Status status, IList lstRecords, DateTime dtTo)
        {
            try
            {
                string subTitle = string.Format("List of Students with {0} Status", status.StatusDesc);

                int cols = 0;
                int rowNum = 7;

                if (status.Id == Constants.Status_CheckOut.Id || status.Id == Constants.Status_CheckIn.Id)
                {
                    cols = 4;
                    objsheet.Cells[rowNum, 1] = "Student ID";
                    objsheet.Cells[rowNum, 2] = "Handset ID";
                    objsheet.Cells[rowNum, 3] = string.Format("{0} Date/Time", status.StatusDesc);
                    objsheet.Cells[rowNum, 4] = "Remark";
                }
                else if (status.Id == Constants.Status_Fault.Id)
                {
                    cols = 6;
                    objsheet.Cells[rowNum, 1] = "Student ID";
                    objsheet.Cells[rowNum, 2] = "Handset ID";
                    objsheet.Cells[rowNum, 3] = string.Format("{0} Date/Time", status.StatusDesc);
                    objsheet.Cells[rowNum, 4] = "Misused?";
                    objsheet.Cells[rowNum, 5] = "Payment Note No.";
                    objsheet.Cells[rowNum, 6] = "Remark";
                }
                else if (status.Id == Constants.Status_Lost.Id)
                {
                    cols = 5;
                    objsheet.Cells[rowNum, 1] = "Student ID";
                    objsheet.Cells[rowNum, 2] = "Handset ID";
                    objsheet.Cells[rowNum, 3] = string.Format("{0} Date/Time", status.StatusDesc);
                    objsheet.Cells[rowNum, 4] = "Payment Note No.";
                    objsheet.Cells[rowNum, 5] = "Remark";
                }

                writtenHeader(subTitle, lstRecords.Count, cols, dtTo, null);

                int count = lstRecords.Count;
                range = objsheet.Range[("A" + rowNum.ToString() + ":D" + Convert.ToString(rowNum)), RuntimeHelpers.GetObjectValue(missingValue)];
                range.Font.Color = Color.Black.ToArgb();
                range.Font.Bold = true;
                range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                range.Cells.Borders.LineStyle = Excel.XlBordersIndex.xlDiagonalDown;
                range.Borders.Color = ColorTranslator.ToOle(Color.Black);
                range = objsheet.Range[("A" + (rowNum + 1).ToString() + ":A" + Convert.ToString(rowNum + 1 + count)), RuntimeHelpers.GetObjectValue(missingValue)];
                range.NumberFormatLocal = "@";
                range.ColumnWidth = 12;
                range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                range = objsheet.Range[("B" + (rowNum + 1).ToString() + ":B" + Convert.ToString(rowNum + 1 + count)), RuntimeHelpers.GetObjectValue(missingValue)];
                range.NumberFormatLocal = "@";
                range.ColumnWidth = 16;
                range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                range = objsheet.Range[("C" + (rowNum + 1).ToString() + ":C" + Convert.ToString(rowNum + 1 + count)), RuntimeHelpers.GetObjectValue(missingValue)];
                range.NumberFormatLocal = "@";
                range.ColumnWidth = 30;
                range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                range = objsheet.Range[("D" + (rowNum + 1).ToString() + ":D" + Convert.ToString(rowNum + 1 + count)), RuntimeHelpers.GetObjectValue(missingValue)];
                range.NumberFormatLocal = "@";
                range.ColumnWidth = 30;
                range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;

                foreach (InventoryTxn txn in lstRecords)
                {
                    rowNum++;

                    if (status.Id == Constants.Status_CheckOut.Id || status.Id == Constants.Status_CheckIn.Id)
                    {
                        objsheet.Cells[rowNum, 1] = txn.StudentID;
                        objsheet.Cells[rowNum, 2] = txn.HandsetID;
                        objsheet.Cells[rowNum, 3] = txn.TxnTime;
                        objsheet.Cells[rowNum, 4] = txn.Remark;
                    }
                    else if (status.Id == Constants.Status_Fault.Id)
                    {
                        objsheet.Cells[rowNum, 1] = txn.StudentID;
                        objsheet.Cells[rowNum, 2] = txn.HandsetID;
                        objsheet.Cells[rowNum, 3] = txn.TxnTime;
                        objsheet.Cells[rowNum, 4] = txn.NeedPay ? "Yes" : "";
                        objsheet.Cells[rowNum, 5] = txn.PaymentNo;
                        objsheet.Cells[rowNum, 6] = txn.Remark;
                    }
                    else if (status.Id == Constants.Status_Lost.Id)
                    {
                        objsheet.Cells[rowNum, 1] = txn.StudentID;
                        objsheet.Cells[rowNum, 2] = txn.HandsetID;
                        objsheet.Cells[rowNum, 3] = txn.TxnTime;
                        objsheet.Cells[rowNum, 4] = txn.PaymentNo;
                        objsheet.Cells[rowNum, 5] = txn.Remark;
                    }

                }

                objsheet.Range["A1:A1", RuntimeHelpers.GetObjectValue(missingValue)].Select();
            }
            catch
            {

            }
        }

        public void RptInvIndivHandset(IList lstRecords, string handsetId)
        {
            try
            {
                objsheet.Cells[1, 2] = "HKUST";
                objsheet.Cells[2, 3] = "List of all historical events on a specific handset";
                objsheet.Cells[3, 3] = "";
                objsheet.Cells[4, 3] = "Date/Time:";
                // objsheet.Cells[5, 3] = "Quantity :";
                objsheet.Cells[4, 4] = handsetId;
                // objsheet.Cells[5, 3] = totalRec;

                range = objsheet.Range["A1:F1", RuntimeHelpers.GetObjectValue(missingValue)];
                range.Merge(true);
                range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
                range.Font.Bold = true;
                range.Font.Size = 15;

                range = objsheet.Range["A1:C6", RuntimeHelpers.GetObjectValue(missingValue)];
                range.Font.Bold = true;
                range = objsheet.Range["B2:E2", RuntimeHelpers.GetObjectValue(missingValue)];
                range.Merge(true);
                range.Font.Bold = true;
                range.HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;

                int rowNum = 7;
                objsheet.Cells[rowNum, 1] = "Event";
                objsheet.Cells[rowNum, 2] = "Student ID";
                objsheet.Cells[rowNum, 3] = "Date/Time";
                objsheet.Cells[rowNum, 4] = "Misused?";
                objsheet.Cells[rowNum, 5] = "Payment Note No.";
                objsheet.Cells[rowNum, 6] = "Remark";

                int count = lstRecords.Count;

                range = objsheet.Range[("A" + rowNum.ToString() + ":F" + Convert.ToString(rowNum)), RuntimeHelpers.GetObjectValue(missingValue)];
                range.Font.Color = Color.Black.ToArgb();
                range.Font.Bold = true;
                range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                range.Cells.Borders.LineStyle = Excel.XlBordersIndex.xlDiagonalDown;
                range.Borders.Color = ColorTranslator.ToOle(Color.Black);

                range = objsheet.Range[("A" + (rowNum + 1).ToString() + ":A" + Convert.ToString(rowNum + 1 + count)), RuntimeHelpers.GetObjectValue(missingValue)];
                range.NumberFormatLocal = "@";
                range.ColumnWidth = 20;
                range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                range = objsheet.Range[("B" + (rowNum + 1).ToString() + ":B" + Convert.ToString(rowNum + 1 + count)), RuntimeHelpers.GetObjectValue(missingValue)];
                range.NumberFormatLocal = "@";
                range.ColumnWidth = 16;
                range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                range = objsheet.Range[("C" + (rowNum + 1).ToString() + ":C" + Convert.ToString(rowNum + 1 + count)), RuntimeHelpers.GetObjectValue(missingValue)];
                range.NumberFormatLocal = "@";
                range.ColumnWidth = 20;
                range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                range = objsheet.Range[("D" + (rowNum + 1).ToString() + ":D" + Convert.ToString(rowNum + 1 + count)), RuntimeHelpers.GetObjectValue(missingValue)];
                range.NumberFormatLocal = "@";
                range.ColumnWidth = 10;
                range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                range = objsheet.Range[("E" + (rowNum + 1).ToString() + ":E" + Convert.ToString(rowNum + 1 + count)), RuntimeHelpers.GetObjectValue(missingValue)];
                range.NumberFormatLocal = "@";
                range.ColumnWidth = 20;
                range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                range = objsheet.Range[("F" + (rowNum + 1).ToString() + ":F" + Convert.ToString(rowNum + 1 + count)), RuntimeHelpers.GetObjectValue(missingValue)];
                range.ColumnWidth = 30;
                range.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
                // list out the records.

                foreach (InventoryTxn txn in lstRecords)
                {
                    rowNum++;
                    objsheet.Cells[rowNum, 1] = txn.UserEvent;
                    objsheet.Cells[rowNum, 2] = txn.StudentID;
                    objsheet.Cells[rowNum, 3] = txn.TxnTime;
                    objsheet.Cells[rowNum, 4] = txn.NeedPay ? "Yes" : "";
                    objsheet.Cells[rowNum, 5] = txn.PaymentNo;
                    objsheet.Cells[rowNum, 6] = txn.Remark;
                }

                objsheet.Range["A1:A1", RuntimeHelpers.GetObjectValue(missingValue)].Select();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                releaseObject(objsheet);
                releaseObject(objWorkbook);
                releaseObject(objExcel);
            }
        }

        public void RptInvIndivStudent(IList lstRecords, string studentId)
        {
            try
            {
                objsheet.Cells[1, 2] = "HKUST";
                objsheet.Cells[2, 3] = "List of all historical events on a specific student";
                objsheet.Cells[3, 3] = "";
                objsheet.Cells[4, 3] = "Student ID:";

                objsheet.Range["D4:D4", RuntimeHelpers.GetObjectValue(missingValue)].NumberFormatLocal = "@";
                objsheet.Range["D4:D4", RuntimeHelpers.GetObjectValue(missingValue)].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;

                // objsheet.Cells[5, 3] = "Quantity :";
                objsheet.Cells[4, 4] = studentId;
                // objsheet.Cells[5, 3] = totalRec;

                range = objsheet.Range["A1:F1", RuntimeHelpers.GetObjectValue(missingValue)];
                range.Merge(true);
                range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
                range.Font.Bold = true;
                range.Font.Size = 15;

                range = objsheet.Range["A1:C6", RuntimeHelpers.GetObjectValue(missingValue)];
                range.Font.Bold = true;
                range = objsheet.Range["B2:E2", RuntimeHelpers.GetObjectValue(missingValue)];
                range.Merge(true);
                range.Font.Bold = true;
                range.HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;

                int rowNum = 7;
                objsheet.Cells[rowNum, 1] = "Event";
                objsheet.Cells[rowNum, 2] = "Handset ID";
                objsheet.Cells[rowNum, 3] = "Date/Time";
                objsheet.Cells[rowNum, 4] = "Misused?";
                objsheet.Cells[rowNum, 5] = "Payment Note No.";
                objsheet.Cells[rowNum, 6] = "Remark";

                int count = lstRecords.Count;

                range = objsheet.Range[("A" + rowNum.ToString() + ":F" + Convert.ToString(rowNum)), RuntimeHelpers.GetObjectValue(missingValue)];
                range.Font.Color = Color.Black.ToArgb();
                range.Font.Bold = true;
                range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                range.Cells.Borders.LineStyle = Excel.XlBordersIndex.xlDiagonalDown;
                range.Borders.Color = ColorTranslator.ToOle(Color.Black);

                range = objsheet.Range[("A" + (rowNum + 1).ToString() + ":A" + Convert.ToString(rowNum + 1 + count)), RuntimeHelpers.GetObjectValue(missingValue)];
                range.NumberFormatLocal = "@";
                range.ColumnWidth = 20;
                range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                range = objsheet.Range[("B" + (rowNum + 1).ToString() + ":B" + Convert.ToString(rowNum + 1 + count)), RuntimeHelpers.GetObjectValue(missingValue)];
                range.NumberFormatLocal = "@";
                range.ColumnWidth = 16;
                range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                range = objsheet.Range[("C" + (rowNum + 1).ToString() + ":C" + Convert.ToString(rowNum + 1 + count)), RuntimeHelpers.GetObjectValue(missingValue)];
                range.NumberFormatLocal = "@";
                range.ColumnWidth = 20;
                range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                range = objsheet.Range[("D" + (rowNum + 1).ToString() + ":D" + Convert.ToString(rowNum + 1 + count)), RuntimeHelpers.GetObjectValue(missingValue)];
                range.NumberFormatLocal = "@";
                range.ColumnWidth = 10;
                range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                range = objsheet.Range[("E" + (rowNum + 1).ToString() + ":E" + Convert.ToString(rowNum + 1 + count)), RuntimeHelpers.GetObjectValue(missingValue)];
                range.NumberFormatLocal = "@";
                range.ColumnWidth = 20;
                range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                range = objsheet.Range[("F" + (rowNum + 1).ToString() + ":F" + Convert.ToString(rowNum + 1 + count)), RuntimeHelpers.GetObjectValue(missingValue)];
                range.ColumnWidth = 30;
                range.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
                // list out the records.

                foreach (InventoryTxn txn in lstRecords)
                {
                    rowNum++;
                    objsheet.Cells[rowNum, 1] = txn.UserEvent;
                    objsheet.Cells[rowNum, 2] = txn.HandsetID;
                    objsheet.Cells[rowNum, 3] = txn.TxnTime;
                    objsheet.Cells[rowNum, 4] = txn.NeedPay ? "Yes" : "";
                    objsheet.Cells[rowNum, 5] = txn.PaymentNo;
                    objsheet.Cells[rowNum, 6] = txn.Remark;
                }

                objsheet.Range["A1:A1", RuntimeHelpers.GetObjectValue(missingValue)].Select();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                releaseObject(objsheet);
                releaseObject(objWorkbook);
                releaseObject(objExcel);
            }
        }
    }
}
